﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ServiceModel;
using System.ServiceModel.Description;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.ServiceModel.Web;
using System.Runtime.Serialization;
using System.ServiceModel.Channels;
using System.Globalization;
using Newtonsoft.Json;
using Library.Font;
using System.Data.SqlClient;

namespace WcfResultPoint
{
    [Serializable]
    public class ResultModel
    {
        public ResultModel(DataTable table)
        {
            this.Table = table;
        }

        public int TotalRecord { get; set; }
        public DataTable Table { get; set; }
    }

    [DataContract]
    public class FileData
    {
        [DataMember]
        public string FileName
        {
            get;
            set;
        }

        [DataMember]
        public byte[] BufferData
        {
            get;
            set;
        }

        [DataMember]
        public int FilePosition
        {
            get;
            set;
        }
    }

    [DataContract]
    public class ErrorDetails
    {
        [DataMember]
        public int ErrorCode
        {
            get;
            set;
        }

        [DataMember]
        public int ErrorMessage
        {
            get;
            set;
        }
    }

    //Define the service contract
    [ServiceContract(Name = "http://WcfResultPoint")]
    public interface IFileProcessing
    {
        [OperationContract]
        string GetEmployees();

        [OperationContract]
        string GetUniversities();

        [OperationContract]
        [FaultContract(typeof(ErrorDetails))]
        bool FileUpload(FileData request);

        [OperationContract]
        string ProcessExcel(string fileName);

        [OperationContract]
        string ProcessVFoxPro(string fileName);

        [OperationContract]
        string StandData(string fileName, IDictionary<string, string> mapFields, string defaultUniversity);

        [OperationContract]
        string GetDataToExport(string fileName, IDictionary<string, string> mapFields, string defaultUniversity);

        [OperationContract]
        ErrorCode ExecuteImportToDB(string fileName, IDictionary<string, string> mapFields,
            string universityId, string universityName, bool check, string defaultUniversity, string employeeId, string note);

        //[OperationContract(Action = "LoadFileFoxpro")]
        //DataTable LoadFileFoxpro(FileUploadMessage fileUploadMessage);
    }

    //This class implements the WCF service
    public class FileProcessingService : IFileProcessing
    {
        const int rowCount = 50;

        const string VFoxproConnectionString = "Provider=vfpoledb;Data Source={0};Collating Sequence=machine;";
        const string Excel97ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Mode=ReadWrite;Extended Properties=\"Excel 8.0;HDR=YES;\"";
        const string Excel07ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
        private string SqlConnectionString = System.Configuration.ConfigurationManager.
                ConnectionStrings["TuyenSinhConnectionString"].ConnectionString;

        readonly string FileUploadPath = System.Configuration.ConfigurationManager.AppSettings["UploadFolder"];

        public string GetEmployees()
        {
            DataTable table = new DataTable();

            using (SqlConnection connection = new SqlConnection(SqlConnectionString))
            {
                try
                {
                    var sql = "SELECT * FROM NhanVien";
                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        connection.Open();
                        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                        {
                            adapter.Fill(table);
                        }
                    }
                }
                catch (Exception)
                {
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                }
            }

            return JsonConvert.SerializeObject(table, Formatting.Indented);
        }

        public string GetUniversities()
        {
            DataTable table = new DataTable();

            using (SqlConnection connection = new SqlConnection(SqlConnectionString))
            {
                try
                {
                    var sql = "SELECT RTRIM(ID) AS ID, Title FROM TruongDaiHoc_Chuan";
                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        connection.Open();
                        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                        {
                            adapter.Fill(table);
                        }
                    }
                }
                catch (Exception)
                {
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                }
            }

            return JsonConvert.SerializeObject(table, Formatting.Indented);
        }

        public bool FileUpload(FileData request)
        {
            string fullPath = Path.Combine(FileUploadPath, request.FileName);

            if (request.FilePosition == 0)
            {
                File.Create(fullPath).Close();
            }

            using (FileStream fileToupload = new FileStream(fullPath, FileMode.Open,
                FileAccess.ReadWrite, FileShare.Read))
            {
                fileToupload.Seek(request.FilePosition, SeekOrigin.Begin);
                fileToupload.Write(request.BufferData, 0, request.BufferData.Length);
                fileToupload.Close();
            }

            return true;
        }

        public string ProcessVFoxPro(string fileName)
        {
            int totalRecord;
            ResultModel result = new ResultModel(GetFoxproData(fileName, out totalRecord))
                {
                    TotalRecord = totalRecord
                };
            return JsonConvert.SerializeObject(result, Formatting.Indented);
        }

        /// <summary>
        /// First, upload file
        /// Second, process file
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="fileStream"></param>
        /// <returns></returns>
        public string ProcessExcel(string fileName)
        {
            int totalRecord;
            ResultModel result = new ResultModel(GetExcelData(fileName, out totalRecord))
                {
                    TotalRecord = totalRecord
                };

            // get some rows to view pattern
            while (result.Table.Rows.Count > rowCount)
            {
                result.Table.Rows.RemoveAt(rowCount);
            }

            return JsonConvert.SerializeObject(result, Formatting.Indented);
        }

        public string StandData(string fileName, IDictionary<string, string> mapFields, string defaultUniversity)
        {
            try
            {
                const int CountPattern = 100;
                DataTable sourceTable;
                FileInfo fileInfo = new FileInfo(fileName);
                int totalRecord;
                switch (fileInfo.Extension)
                {
                    case ".xls":
                    case ".xlsx":
                        sourceTable = GetExcelData(fileName, out totalRecord);
                        break;
                    case ".dbf":
                        sourceTable = GetFoxproData(fileName, out totalRecord);
                        break;
                    default:
                        sourceTable = new DataTable();
                        totalRecord = 0;
                        break;
                }

                // Standard
                ConvertFont convertFont = new ConvertFont(ConvertFont.VietEncodings.TCVN3);
                int divide = 1, i = 0, count = Math.Min(CountPattern, sourceTable.Rows.Count);
                while (i < count)
                {
                    float? d = ConvertMark(sourceTable.Rows[i++][mapFields[Fields.Std.DM1]], divide);
                    if (d > 100)
                    {
                        divide = 100;
                        break;
                    }
                }

                ResultModel result = new ResultModel(GetStandardList(convertFont, sourceTable, divide, mapFields, defaultUniversity))
                    {
                        TotalRecord = totalRecord
                    };
                return JsonConvert.SerializeObject(result, Formatting.Indented);
            }
            catch (Exception)
            {
                return string.Empty;
            }
        }

        public string GetDataToExport(string fileName, IDictionary<string, string> mapFields, string defaultUniversity)
        {
            const int CountPattern = 100;
            DataTable sourceTable;
            FileInfo fileInfo = new FileInfo(fileName);
            int totalRecord;
            switch (fileInfo.Extension)
            {
                case ".xls":
                case ".xlsx":
                    sourceTable = GetExcelData(fileName, out totalRecord, false);
                    break;
                case ".dbf":
                    sourceTable = GetFoxproData(fileName, out totalRecord, false);
                    break;
                default:
                    sourceTable = new DataTable();
                    totalRecord = 0;
                    break;
            }

            // Standard
            ConvertFont convertFont = new ConvertFont(ConvertFont.VietEncodings.TCVN3);
            int divide = 1, i = 0, count = Math.Min(CountPattern, sourceTable.Rows.Count);
            while (i < count)
            {
                float? d = ConvertMark(sourceTable.Rows[i++][mapFields[Fields.Std.DM1]], divide);
                if (d > 100)
                {
                    divide = 100;
                    break;
                }
            }

            ResultModel result = new ResultModel(GetStandardList(convertFont, sourceTable, divide, mapFields, defaultUniversity))
                    {
                        TotalRecord = totalRecord
                    };
            return JsonConvert.SerializeObject(result, Formatting.Indented);
        }

        public ErrorCode ExecuteImportToDB(string fileName, IDictionary<string, string> mapFields,
            string universityId, string universityName, bool check, string defaultUniversity, string employeeId, string note)
        {
            const int CountPattern = 100;
            DataTable sourceTable;
            FileInfo fileInfo = new FileInfo(fileName);
            int totalRecord;
            switch (fileInfo.Extension)
            {
                case ".xls":
                case ".xlsx":
                    sourceTable = GetExcelData(fileName, out totalRecord, false);
                    break;
                case ".dbf":
                    sourceTable = GetFoxproData(fileName, out totalRecord, false);
                    break;
                default:
                    sourceTable = new DataTable();
                    totalRecord = 0;
                    break;
            }

            // Standard
            ConvertFont convertFont = new ConvertFont(ConvertFont.VietEncodings.TCVN3);
            int divide = 1, i = 0, count = Math.Min(CountPattern, sourceTable.Rows.Count);
            while (i < count)
            {
                float? d = ConvertMark(sourceTable.Rows[i++][mapFields[Fields.Std.DM1]], divide);
                if (d > 100)
                {
                    divide = 100;
                    break;
                }
            }

            DataTable table = GetStandardList(convertFont, sourceTable, divide, mapFields, defaultUniversity);
            return ImportToSqlServer(fileName, table, universityId, universityName, employeeId, note, check);
        }

        private DataTable GetFoxproData(string fileName, out int totalRecord, bool isSample = true)
        {
            DataTable table = new DataTable();
            totalRecord = 0;

            using (OleDbConnection connection = new OleDbConnection(string.Format(VFoxproConnectionString, FileUploadPath)))
            {
                try
                {
                    var sql = "SELECT * FROM " + fileName;
                    using (OleDbCommand command = new OleDbCommand(sql, connection))
                    {
                        connection.Open();
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
                        {
                            adapter.Fill(table);
                        }
                    }
                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                }
            }

            totalRecord = table.Rows.Count;

            if (isSample)
            {
                while (table.Rows.Count > rowCount)
                {
                    table.Rows.RemoveAt(rowCount);
                }
            }

            return table;
        }

        private DataTable GetExcelData(string fileName, out int totalRecord, bool isSample = true)
        {
            string fullPath = Path.Combine(FileUploadPath, fileName);

            totalRecord = 0;

            string connectionString = fullPath.EndsWith(".xls", true, CultureInfo.InvariantCulture) ? Excel97ConnectionString : Excel07ConnectionString;

            DataTable table = new DataTable();
            using (OleDbConnection connection = new OleDbConnection(string.Format(connectionString, fullPath)))
            {
                try
                {
                    string sheetName = string.Empty;
                    connection.Open();
                    DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    if (schemaTable != null && schemaTable.Rows.Count > 0)
                    {
                        // get first sheet
                        sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString();
                    }

                    var sql = "SELECT * FROM [" + (sheetName.EndsWith("$") ? sheetName : (sheetName + '$')) + "]";
                    using (OleDbCommand command = new OleDbCommand(sql, connection))
                    {
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
                        {
                            adapter.Fill(table);
                        }
                    }
                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                }
            }

            totalRecord = table.Rows.Count;

            if (isSample)
            {
                while (table.Rows.Count > rowCount)
                {
                    table.Rows.RemoveAt(rowCount);
                }
            }

            return table;
        }

        private DataTable GetStandardList(ConvertFont convertFont, DataTable sourceTable, int divide, IDictionary<string, string> mapFields, string defaultUniversity)
        {
            DataTable table = new DataTable("ThiSinh_DaiHoc");
            table.Columns.Add(Fields.Std.SBD);
            table.Columns.Add(Fields.Std.HoTen);
            table.Columns.Add(Fields.Std.NgaySinh);
            table.Columns.Add(Fields.Std.MaNganh);
            table.Columns.Add(Fields.Std.MaTruong);
            table.Columns.Add(Fields.Std.KhoiThi);
            table.Columns.Add(Fields.Std.DM1);
            table.Columns.Add(Fields.Std.DM2);
            table.Columns.Add(Fields.Std.DM3);
            table.Columns.Add(Fields.Std.DM4);
            table.Columns.Add(Fields.Std.DM5);
            table.Columns.Add(Fields.Std.DM6);
            table.Columns.Add(Fields.Std.TongDiem);

            foreach (DataRow item in sourceTable.Rows)
            {
                if (item[mapFields[Fields.Std.SBD]] == DBNull.Value
                    || item[mapFields[Fields.Std.SBD]].ToString().Trim().Length == 0) continue;

                DataRow row = table.NewRow();
                row[Fields.Std.SBD] = item[mapFields[Fields.Std.SBD]];
                row[Fields.Std.MaTruong] = (defaultUniversity == string.Empty ? item[mapFields[Fields.Std.MaTruong]].ToString().Trim() : defaultUniversity);

                // Aditional

                if (sourceTable.Columns.Contains(mapFields[Fields.Std.HoTen]))
                {
                    string hoten = convertFont.Convert(item[mapFields[Fields.Std.HoTen]].ToString()).Trim();
                    if (!string.IsNullOrEmpty(mapFields[Fields.Std.Ten]))
                    {
                        hoten += ' ' + convertFont.Convert(item[mapFields[Fields.Std.Ten]].ToString()).Trim();
                    }
                    row[Fields.Std.HoTen] = hoten;
                }
                if (sourceTable.Columns.Contains(mapFields[Fields.Std.KhoiThi]))
                {
                    row[Fields.Std.KhoiThi] = item[mapFields[Fields.Std.KhoiThi]];
                }
                if (sourceTable.Columns.Contains(mapFields[Fields.Std.NgaySinh]))
                {
                    row[Fields.Std.NgaySinh] = ConvertBirthDay(item[mapFields[Fields.Std.NgaySinh]]);
                }
                if (sourceTable.Columns.Contains(mapFields[Fields.Std.MaNganh]))
                {
                    row[Fields.Std.MaNganh] = item[mapFields[Fields.Std.MaNganh]];
                }
                if (sourceTable.Columns.Contains(mapFields[Fields.Std.DM1]))
                {
                    row[Fields.Std.DM1] = ConvertMark(item[mapFields[Fields.Std.DM1]], divide);
                }
                if (sourceTable.Columns.Contains(mapFields[Fields.Std.DM2]))
                {
                    row[Fields.Std.DM2] = ConvertMark(item[mapFields[Fields.Std.DM2]], divide);
                }
                if (sourceTable.Columns.Contains(mapFields[Fields.Std.DM3]))
                {
                    row[Fields.Std.DM3] = ConvertMark(item[mapFields[Fields.Std.DM3]], divide);
                }
                if (sourceTable.Columns.Contains(mapFields[Fields.Std.DM4]))
                {
                    row[Fields.Std.DM4] = ConvertMark(item[mapFields[Fields.Std.DM4]], divide);
                }
                if (sourceTable.Columns.Contains(mapFields[Fields.Std.DM5]))
                {
                    row[Fields.Std.DM5] = ConvertMark(item[mapFields[Fields.Std.DM5]], divide);
                }
                if (sourceTable.Columns.Contains(mapFields[Fields.Std.DM6]))
                {
                    row[Fields.Std.DM6] = ConvertMark(item[mapFields[Fields.Std.DM6]], divide);
                }
                if (sourceTable.Columns.Contains(mapFields[Fields.Std.TongDiem]))
                {
                    row[Fields.Std.TongDiem] = ConvertMark(item[mapFields[Fields.Std.TongDiem]], divide);
                }

                table.Rows.Add(row);
            }

            return table;
        }

        private float? ConvertMark(object item, int divide)
        {
            float? diem = null;
            if (item != DBNull.Value && item.ToString().Trim().Length > 0)
            {
                string value = item.ToString().Trim();
                float f;
                if (float.TryParse(value, out f))
                {
                    diem = Convert.ToSingle(item);
                    diem /= divide;
                }
                else if (value == ".")
                {
                    diem = 0;
                }
            }
            return diem;
        }

        private string ConvertBirthDay(object item)
        {
            if (item == DBNull.Value || item.Equals(string.Empty))
                return null;

            string date = item.ToString().Trim();

            //if (rdoFormatDate.SelectedIndex == 1)
            //{
            //    string dd = date.Substring(0, 2);
            //    date = date.Remove(0, 2).Insert(2, dd);
            //}

            if (date.Contains('.'))
            {
                return date.Replace('.', '/');
            }
            else if (date.Contains('/'))
            {
                return date;
            }
            else if (date.Length == 6 || date.Length == 8)
            {
                return date.Insert(4, "/").Insert(2, "/");
            }
            else if (date.Length == 4)
            {
                return date.Insert(2, "/");
            }
            else
            {
                return date;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="table"></param>
        /// <param name="universityId"></param>
        /// <param name="universityName"></param>
        /// <returns></returns>
        private ErrorCode ImportToSqlServer(string fileName, DataTable table, string universityId, string universityName, string employeeId, string note, bool check = true)
        {
            if (table == null || table.Rows.Count == 0)
            {
                return ErrorCode.NotFound;
            }

            // Copy the DataTable to SQL Server
            using (SqlConnection dbConnection = new SqlConnection(SqlConnectionString))
            {
                dbConnection.Open();
                using (SqlTransaction transaction = dbConnection.BeginTransaction())
                {
                    try
                    {
                        int i = 0;

                        string sql;

                        if (check)
                        {
                            sql = "SELECT COUNT(1) FROM ThiSinh_DaiHoc WHERE ID_Truong = @ID AND SBD = @SBD AND ISNULL(KhoiThi, '') = @KhoiThi";
                            using (SqlCommand command = new SqlCommand(sql, dbConnection))
                            {
                                command.Transaction = transaction;
                                command.Parameters.AddWithValue("@ID", universityId);
                                command.Parameters.AddWithValue("@SBD", table.Rows[0][Fields.Std.SBD].ToString());
                                string khoiThi = table.Rows[0][Fields.Std.KhoiThi] == DBNull.Value ? string.Empty : table.Rows[0][Fields.Std.KhoiThi].ToString().Trim();
                                command.Parameters.AddWithValue("@KhoiThi", khoiThi);

                                using (SqlDataReader reader = command.ExecuteReader())
                                {
                                    reader.Read();
                                    i = reader.GetInt32(0);
                                    reader.Close();
                                }
                            }
                            if (i > 0) return ErrorCode.ExistResult;

                            sql = "SELECT COUNT(1) FROM ThiSinh_DaiHoc WHERE ID_Truong = @ID";
                            using (SqlCommand command = new SqlCommand(sql, dbConnection))
                            {
                                command.Transaction = transaction;
                                command.Parameters.AddWithValue("@ID", universityId);
                                using (SqlDataReader reader = command.ExecuteReader())
                                {
                                    reader.Read();
                                    i = reader.GetInt32(0);
                                    reader.Close();
                                }
                            }
                            // The result of this university is exists
                            if (i > 0) return ErrorCode.ExistUniversity;
                        }

                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(dbConnection, SqlBulkCopyOptions.KeepIdentity, transaction))
                        {
                            bulkCopy.DestinationTableName = table.TableName;
                            foreach (var column in table.Columns)
                                bulkCopy.ColumnMappings.Add(column.ToString(), column.ToString());
                            bulkCopy.WriteToServer(table);
                        }

                        sql = "IF NOT EXISTS (SELECT * FROM TruongDaiHoc WHERE RTRIM(ID) = @ID)" + Environment.NewLine
                            + "INSERT INTO TruongDaiHoc (ID, Title) VALUES (@ID, @Title)" + Environment.NewLine
                            + "ELSE" + Environment.NewLine
                            + "UPDATE TruongDaiHoc SET Title = @Title WHERE RTRIM(ID) = @ID" + Environment.NewLine;

                        using (SqlCommand command = new SqlCommand(sql, dbConnection))
                        {
                            command.Transaction = transaction;
                            command.Parameters.AddWithValue("@ID", universityId);
                            command.Parameters.AddWithValue("@Title", universityName);
                            i = command.ExecuteNonQuery();

                            command.CommandText = "IF NOT EXISTS (SELECT * FROM TruongDaiHoc_Chuan WHERE RTRIM(ID) = @ID)" + Environment.NewLine
                            + "INSERT INTO TruongDaiHoc_Chuan (ID, Title) VALUES (@ID, @Title)" + Environment.NewLine
                            + "ELSE" + Environment.NewLine
                            + "UPDATE TruongDaiHoc_Chuan SET Title = @Title WHERE RTRIM(ID) = @ID" + Environment.NewLine;
                            i = command.ExecuteNonQuery();
                        }

                        int emplId = 0;

                        if (int.TryParse(employeeId, out emplId))
                        {

                            sql = "INSERT INTO NhanVienGuiFile (MSNV, ID_Truong, GhiChu, FileName)" + Environment.NewLine
                                + "VALUES (@MSNV, @ID_Truong, @GhiChu, @FileName)";

                            using (SqlCommand command = new SqlCommand(sql, dbConnection))
                            {
                                command.Transaction = transaction;
                                command.Parameters.AddWithValue("@MSNV", emplId);
                                command.Parameters.AddWithValue("@ID_Truong", universityId);
                                command.Parameters.AddWithValue("@GhiChu", note);
                                command.Parameters.AddWithValue("@FileName", fileName);
                                command.ExecuteNonQuery();
                            }
                        }

                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();

                        return ErrorCode.Exception;
                    }
                }
            }

            return ErrorCode.Success;
        }
    }

    public enum ErrorCode
    {
        Success = 200,
        ExistResult = -1,
        ExistUniversity = -2,
        NotFound = -3,
        Exception = -4
    }

    class Program
    {
        static void Main(string[] args)
        {
            //This example WCF service is self-hosted in
            //the command console. This procedure runs the
            //service until the user presses a key

            //This is the address for the WCF service
            Uri baseAddress = new Uri("http://localhost:6666/WcfResultPointService");
            ServiceHost selfHost = new ServiceHost(typeof(FileProcessingService), baseAddress);
            try
            {
                //Create an endpoint
                WSHttpBinding wsHttpBinding = new WSHttpBinding();
                wsHttpBinding.MaxReceivedMessageSize = int.MaxValue;
                wsHttpBinding.ReaderQuotas.MaxStringContentLength = int.MaxValue;

                selfHost.AddServiceEndpoint(typeof(IFileProcessing), wsHttpBinding, "FileProcessingService");
                //Enable the service to exchange its metadata
                ServiceMetadataBehavior smb = new ServiceMetadataBehavior();
                smb.HttpGetEnabled = true;

                //ServiceDebugBehavior sdb = new ServiceDebugBehavior();
                //sdb.IncludeExceptionDetailInFaults = true;

                selfHost.Description.Behaviors.Add(smb);
                //selfHost.Description.Behaviors.Add(sdb);

                //Open the service and tell the user
                selfHost.Open();
                Console.WriteLine("The Host Service is ready");
                Console.WriteLine("Press any key to close the service");
                //Wait for the user to press a key
                Console.ReadKey();
                //Close the service
                selfHost.Close();
            }
            catch (CommunicationException e)
            {
                Console.WriteLine("A communication exception occurred: {0}", e.Message);
                selfHost.Abort();
            }
        }
    }

}
